<?php

include_once '../Connection.php';
include_once 'Database.php';

class ModelCreator_Database_SGBD_Postgre {

    private $conn;
    public $_test;
    public $_allTables;
    public $_privileges = true;
    public $_allColumns;
    public $_allPrimaryKeys;

    public function __construct($host, $port, $db, $user, $password, $schema, $arrTables = NULL) {
        $this->connect($host, $port, $db, $user, $password, $schema);
        if ($this->testConnection()) {
            $this->getAllTables($schema, $arrTables);
            $this->getAllColumns($this->_allTables);
            $this->getPrimaryKeys($this->_allTables, $schema);
        }
    }

    private function connect($host, $port, $db, $user, $password) {

        $this->conn = pg_connect("host=$host user =$user
                password =$password dbname =$db");
        return $this->conn;
    }

    private function testConnection() {
        if (!$this->conn) {
            $this->_test = false;
            return FALSE;
        } else {
            $this->_test = true;
            return TRUE;
        }
    }

    public function getAllTables($schema, $arrTables) {
        $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{$schema}'";
        if (!isset($arrTables['allTables']) && $arrTables['allTables'] != 'ok' && !is_null($arrTables)) {
            unset($arrTables['allTables']);
            $sql .= " and table_name in (";
            $end = end($arrTables);
            foreach ($arrTables as $value) {
                if ($value == $end) {
                    $sql .= "'{$value}')";
                } else {
                    $sql .= "'{$value}',";
                }
            }
        }
        
        $result = pg_query($this->conn, $sql);
        if (!$result) {
            echo "An error occured.\n";
            exit;
        }
        $i = 0;
        
        while ($array = pg_fetch_array($result, null, PGSQL_ASSOC)) {
            $arrayResult[$i] = $array['table_name'];
            $i++;
        }
        $this->_allTables = $arrayResult;
    }
    
    
    public function getAllColumns($tables) {
        foreach ($tables as $value) {

            $sql = "SELECT
                    A.ATTNAME AS \"COLUMN\",
                    PG_CATALOG.FORMAT_TYPE(A.ATTTYPID, A.ATTTYPMOD) AS \"DATATYPE\"
    
                    FROM
                        PG_CATALOG.PG_ATTRIBUTE A
                    WHERE
                        A.ATTNUM > 0
                        AND NOT A.ATTISDROPPED
                        AND A.ATTRELID = (
                            SELECT C.OID
                            FROM PG_CATALOG.PG_CLASS C
                                LEFT JOIN PG_CATALOG.PG_NAMESPACE N ON N.OID = C.RELNAMESPACE
                            WHERE C.RELNAME = '{$value}')";

            $result = pg_query($this->conn, $sql);
            if (!$result) {
                echo "An error occured.\n";
                exit;
            }
            $i = 0;
            while ($array = pg_fetch_array($result, null, PGSQL_ASSOC)) {
                $arrayResult[$value][$i]['COLUMN'] = $array['COLUMN'];
                $arrayResult[$value][$i]['DATATYPE'] = $array['DATATYPE'];
                $i++;
            }
        }
        $this->_allColumns = $arrayResult;
    }

    public function getPrimaryKeys($tables, $schema) {

        foreach ($tables as $key => $value) {

            $sql = "SELECT DISTINCT ON (KC.COLUMN_NAME) KC.COLUMN_NAME
                FROM 
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KC 
                ON KC.TABLE_NAME = TC.TABLE_NAME AND KC.TABLE_SCHEMA = TC.TABLE_SCHEMA
                WHERE 
                    TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
                    AND KC.POSITION_IN_UNIQUE_CONSTRAINT IS NULL
                    AND TC.TABLE_NAME = '{$value}'
                    AND TC.TABLE_SCHEMA = '{$schema}'";

            $result = pg_query($this->conn, $sql);
            if (!$result) {
                echo "An error occured.\n";
                exit;
            }
            $i = 0;
            while ($array = pg_fetch_array($result, null, PGSQL_ASSOC)) {
                $arrayResult[$value][$i] = $array['column_name'];
                $i++;
            }
        }
        $this->_allPrimaryKeys = $arrayResult;
    }

}
